﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace KTVProject.handle
{
    class DBhandle
    {
        static string url = "Data Source=192.168.1.10;Initial Catalog=KTVDB;User ID=sa;Password=123456@qwe";
        public static SqlConnection connection = new SqlConnection(url);

        public object ExecuteScalar(string userName,string pwd)   //执行返回单个值的sql语句
        {
            string sql = $"select count(*) from [dbo].[t_admin] where [a_admin] = N'{userName}' and [a_pwd] = N'{pwd}';";
            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand(sql, connection);

                return command.ExecuteScalar();
            }
            catch (Exception)
            {

                throw;
            }
        }



        public SqlDataAdapter GetMobileBrand()    //获取手机品牌
        {
            try
            {
                string sql = "select * from [dbo].[t_mobileBrand];";
                SqlDataAdapter adapter = new SqlDataAdapter(sql, connection);

                return adapter;
            }
            catch
            {
                MessageBox.Show("异常提示", "数据库操作异常", MessageBoxButtons.OK, MessageBoxIcon.Hand);

                return null;
            }
            finally
            {

            }
        }

        public int GetAddMobileInfo(int brandId,string type,int price,string size,int weight,string style)   //获取添加信息
        {
            string sql = string.Format("insert into [dbo].[t_mobileInfo] values({0},N'{1}',{2},N'{3}',{4},N'{5}');",
                brandId, type, price, size, weight, style);
            connection.Open();

            try
            {
                SqlCommand command = new SqlCommand(sql, connection);

                return command.ExecuteNonQuery();
            }
            catch
            {
                MessageBox.Show("异常提示", "数据库操作异常", MessageBoxButtons.OK, MessageBoxIcon.Hand);

                return 0;
            }
            finally
            {

            }
        }

        public SqlDataReader GetMobileInfo(int brand)   //读取手机信息
        {
            connection.Open();
            string sql = "";

            try
            {
                if (brand == -1)
                {
                    sql = string.Format(@"select b.[brand],[type],[price],[size],[weight],[style] from
                        [dbo].[t_mobileInfo] as i,[dbo].[t_mobileBrand] as b
                        where [brand_id] = b.[id];");
                }
                else
                {
                    sql = string.Format(@"select b.[brand],[type],[price],[size],[weight],[style] from
                        [dbo].[t_mobileInfo] as i,[dbo].[t_mobileBrand] as b
                        where [brand_id] = b.[id] and [brand_id] = {0};", brand);
                }

                SqlCommand command = new SqlCommand(sql, connection);

                return command.ExecuteReader();
            }
            catch
            {
                MessageBox.Show("异常提示", "数据库操作异常", MessageBoxButtons.OK, MessageBoxIcon.Hand);

                return null;
            }
            finally
            {

            }
        }
    }
}
